package example;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author tongchen
 * @create 2022-11-07 19:40
 * 用来测试brand的增删改查
 */
public class BrandTest {
    @Test
    public void testBrand() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //1.建立连接
        String url="jdbc:mysql://127.0.0.1:3306/exer?useSSL=false";
        String name="root";
        String password="abc123";
        Connection conn = DriverManager.getConnection(url, name, password);
        //2.定义收sql语句
        String sql="SELECT * FROM tb_brand";
        //3.建立sql对象
        Statement stmt = conn.createStatement();
        //4.设置参数
        //5.执行sql
        ResultSet res = stmt.executeQuery(sql);
        //6.处理结果
        List<Brand>list= new ArrayList<>();

        //6.1接收结果
        while(res.next()){
            Brand brand = new Brand();
            int id = res.getInt("id");
            String brand_name = res.getString("brand_name");
            String company_name = res.getString("company_name");
            int ordered = res.getInt("ordered");
            String description = res.getString("description");
            int status = res.getInt("status");
            brand.setId(id);
            brand.setBrand_name(brand_name);
            brand.setCompany_name(company_name);
            brand.setOrdered(ordered);
            brand.setStatus(status);
            //6.2封装集合
            list.add(brand);
        }
        System.out.println(list);
        //7.关闭资源
        res.close();
        stmt.close();
        conn.close();

    }
    @Test
    /**
     * 测试添加功能
     */
    public void testAddBrand() throws Exception {
        //接收前端所传来的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int orderd=1;
        String description="销量高达绕地球一圈" ;
        int status=1;
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //1.建立连接
        String url="jdbc:mysql://127.0.0.1:3306/exer?useSSL=false";
        String name="root";
        String password="abc123";
        Connection conn = DriverManager.getConnection(url, name, password);
        //2.定义收sql语句
        String sql="insert into tb_brand(brand_name,\n" +
                "company_name, ordered, description, status)\n" +
                "values(?,?,?,?,?);";
        //3.建立sql对象
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        //4.设置参数
        preparedStatement.setString(1,brandName);
        preparedStatement.setString(2,companyName);
        preparedStatement.setInt(3,orderd);
        preparedStatement.setString(4,description);
        preparedStatement.setInt(5,status);
        //5.执行sql
        int i = preparedStatement.executeUpdate();
        //6.处理结果
        if(i>0){
            System.out.println(true);
        }
        else {
            System.out.println(false);
        }
        //7.关闭资源
        preparedStatement.close();
        conn.close();

    }
    @Test
    /**
     * 测试修改功能
     */
    public void testModifyBrand() throws Exception {
        //接收前端所传来的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int orderd=1;
        String description="销量高达绕地球3圈" ;
        int status=1000;
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //1.建立连接
        String url="jdbc:mysql://127.0.0.1:3306/exer?useSSL=false";
        String name="root";
        String password="abc123";
        Connection conn = DriverManager.getConnection(url, name, password);
        //2.定义收sql语句
      String sql="update tb_brand\n" +
              "  set brand_name = ?,\n" +
              " company_name= ?,\n" +
              " ordered  = ?,\n" +
              " description = ?,\n" +
              " status  = ?\n" +
              "  where id = ?";
        //3.建立sql对象
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        //4.设置参数
        preparedStatement.setString(1,brandName);
        preparedStatement.setString(2,companyName);
        preparedStatement.setInt(3,orderd);
        preparedStatement.setString(4,description);
        preparedStatement.setInt(5,status);
        preparedStatement.setInt(6,4);
        //5.执行sql
        int i = preparedStatement.executeUpdate();
        //6.处理结果
        if(i>0){
            System.out.println(true);
        }
        else {
            System.out.println(false);
        }
        //7.关闭资源
        preparedStatement.close();
        conn.close();

    }
    @Test
    /**
     * 测试删除功能
     */
    public void testDeleteBrand() throws Exception {
        //接收前端所传来的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int orderd=1;
        String description="销量高达绕地球3圈" ;
        int status=1000;
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //1.建立连接
        String url="jdbc:mysql://127.0.0.1:3306/exer?useSSL=false";
        String name="root";
        String password="abc123";
        Connection conn = DriverManager.getConnection(url, name, password);
        //2.定义收sql语句
        String sql="delete from tb_brand where id = ?";
        //3.建立sql对象
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        //4.设置参数
        preparedStatement.setInt(1,4);

        //5.执行sql
        int i = preparedStatement.executeUpdate();
        //6.处理结果
        if(i>0){
            System.out.println(true);
        }
        else {
            System.out.println(false);
        }
        //7.关闭资源
        preparedStatement.close();
        conn.close();

    }

}
//创建品牌类
class Brand {
    // id 主键
    private int id;
    // 品牌名称
    private String brand_name;
    // 企业名称
    private String company_name;
    // 排序字段
    private int ordered;
    // 描述信息
    private String description;
    // 状态：0：禁用  1：启动
    private int status;
    //重写get和set方法，便于后来接收数据库中返回的数据


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrand_name() {
        return brand_name;
    }

    public void setBrand_name(String brand_name) {
        this.brand_name = brand_name;
    }

    public String getCompany_name() {
        return company_name;
    }

    public void setCompany_name(String company_name) {
        this.company_name = company_name;
    }

    public int getOrdered() {
        return ordered;
    }

    public void setOrdered(int ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brand_name='" + brand_name + '\'' +
                ", company_name='" + company_name + '\'' +
                ", ordered='" + ordered + '\'' +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}
